-- ============================================================================
-- 宠物管理系统 - 搜索历史表 (PostgreSQL版本)
-- ============================================================================
-- 功能说明：
--   1. 记录用户的搜索历史
--   2. 统计搜索关键词的热度
--   3. 支持搜索建议功能
--   4. 记录不同类型的搜索（商品、服务等）
-- ============================================================================

-- 创建搜索历史表
CREATE TABLE IF NOT EXISTS search_history (
    -- 主键ID，自增长
    id BIGSERIAL PRIMARY KEY,
    
    -- 用户ID，关联用户表
    user_id BIGINT NOT NULL,
    
    -- 搜索关键词，最多100个字符
    keyword VARCHAR(100) NOT NULL,
    
    -- 搜索类型：all-全部, product-商品, service-服务
    search_type VARCHAR(20) NOT NULL DEFAULT 'all',
    
    -- 搜索次数统计，默认为1，每次搜索相同关键词时累加
    search_count INTEGER NOT NULL DEFAULT 1,
    
    -- 创建时间，记录首次搜索时间
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    -- 更新时间，记录最后一次搜索时间
    update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- ============================================================================
-- 索引创建
-- ============================================================================

-- 用户ID索引：加速按用户查询搜索历史
CREATE INDEX IF NOT EXISTS idx_search_history_user_id ON search_history(user_id);

-- 关键词索引：用于搜索建议和热词统计
CREATE INDEX IF NOT EXISTS idx_search_history_keyword ON search_history(keyword);

-- 搜索类型索引：按类型筛选搜索记录
CREATE INDEX IF NOT EXISTS idx_search_history_search_type ON search_history(search_type);

-- 更新时间索引：按时间排序查询最近搜索
CREATE INDEX IF NOT EXISTS idx_search_history_update_time ON search_history(update_time DESC);

-- 复合索引：优化用户+关键词+类型的组合查询（用于去重和更新）
CREATE INDEX IF NOT EXISTS idx_search_history_user_keyword_type 
    ON search_history(user_id, keyword, search_type);

-- ============================================================================
-- 表注释
-- ============================================================================

COMMENT ON TABLE search_history IS '搜索历史表 - 记录用户搜索行为和关键词热度';
COMMENT ON COLUMN search_history.id IS '主键ID';
COMMENT ON COLUMN search_history.user_id IS '用户ID';
COMMENT ON COLUMN search_history.keyword IS '搜索关键词';
COMMENT ON COLUMN search_history.search_type IS '搜索类型（all-全部, product-商品, service-服务）';
COMMENT ON COLUMN search_history.search_count IS '搜索次数统计';
COMMENT ON COLUMN search_history.create_time IS '首次搜索时间';
COMMENT ON COLUMN search_history.update_time IS '最后搜索时间';

-- ============================================================================
-- 触发器：自动更新update_time
-- ============================================================================

-- 创建触发器函数
CREATE OR REPLACE FUNCTION update_search_history_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.update_time = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
DROP TRIGGER IF EXISTS trigger_update_search_history_timestamp ON search_history;
CREATE TRIGGER trigger_update_search_history_timestamp
    BEFORE UPDATE ON search_history
    FOR EACH ROW
    EXECUTE FUNCTION update_search_history_timestamp();

-- ============================================================================
-- 使用说明
-- ============================================================================
-- 1. 执行本脚本创建表和索引
-- 2. 应用程序会自动记录用户搜索行为
-- 3. 相同用户搜索相同关键词时，search_count会自动累加
-- 4. update_time会通过触发器自动更新
-- 
-- 性能优化建议：
-- 1. 定期清理旧数据（如保留最近6个月）
-- 2. 定期执行 VACUUM ANALYZE search_history; 优化表性能
-- 3. 监控索引使用情况，必要时调整索引策略
-- ============================================================================
